from datetime import datetime
from typing import Optional, Dict, Any, List, Tuple
from apps.models.models import Coach, User, User_Profiles
from apps.schemas.schemas import CoachCreateRequest, CoachUpdateRequest
from tortoise.transactions import in_transaction


async def get_all_coaches(
        username: Optional[str] = None,
        search: Optional[str] = None,
        page: int = 1,
        page_size: int = 10
) -> Tuple[int, List[Dict[str, Any]]]:
    """
    获取所有教练列表（管理员端使用）
    """
    # 构建基础查询
    query = Coach.all().select_related('user_id')

    # 如果提供了 username，进行模糊匹配
    if username:
        query = query.filter(
            user_id__username__icontains=username
        )
    # 如果提供了 search，进行模糊匹配（按姓名搜索）
    elif search:
        query = query.filter(
            user_id__profiles__fullname__icontains=search
        )

    # 获取总数量
    total = await query.count()

    # 分页查询
    coaches = await query.offset((page - 1) * page_size).limit(page_size)

    data = []

    for coach in coaches:
        profile = await User_Profiles.get_or_none(user_id=coach.user_id.user_id)
        if not profile:
            continue

        data.append({
            "coach_id": coach.coach_id,
            "fullname": profile.fullname,
            "gender": profile.gender,
            "email": coach.user_id.email,
            "specialization": coach.specialization,
            "license_number": coach.license_number,
            "username": coach.user_id.username,
            "phone": coach.user_id.phone,
            "rating": coach.rating,
            "years_of_experience": coach.years_of_experience,
            "birthday": profile.birthday,
            "address": profile.address
        })

    return total, data


async def get_all_coaches_for_student(
        search: Optional[str] = None,
        page: int = 1,
        page_size: int = 10
) -> Tuple[int, List[Dict[str, Any]]]:
    """
    获取所有教练列表（学员端使用）
    """
    # 构建基础查询
    query = Coach.all().select_related('user_id')

    # 如果提供了搜索关键字，进行模糊匹配（只根据姓名搜索）
    if search:
        query = query.filter(
            user_id__profiles__fullname__icontains=search  # 只按全名搜索
        )

    # 获取总数量
    total = await query.count()

    # 分页查询
    coaches = await query.offset((page - 1) * page_size).limit(page_size)

    data = []

    for coach in coaches:
        profile = await User_Profiles.get_or_none(user_id=coach.user_id.user_id)
        if not profile:
            continue

        data.append({
            "coach_id": coach.coach_id,
            "fullname": profile.fullname,
            "gender": profile.gender,
            "email": coach.user_id.email,
            "specialization": coach.specialization,
            "license_number": coach.license_number,
            "username": coach.user_id.username,
            "phone": coach.user_id.phone,
            "rating": coach.rating,
            "years_of_experience": coach.years_of_experience,
            "birthday": profile.birthday,
            "address": profile.address
        })

    return total, data


async def get_coach_by_id(coach_id: int) -> Optional[Dict[str, Any]]:
    """
    根据教练ID获取教练详细信息
    """
    coach = await Coach.get_or_none(coach_id=coach_id).select_related('user_id')
    if not coach:
        return None

    profile = await User_Profiles.get_or_none(user_id=coach.user_id.user_id)
    if not profile:
        return None

    return {
        "coach_id": coach.coach_id,
        "password": coach.user_id.password,
        "hire_date": coach.hire_date,
        "fullname": profile.fullname,
        "email": coach.user_id.email,
        "gender": profile.gender,
        "rating": coach.rating,
        "license_number": coach.license_number,
        "username": coach.user_id.username,
        "phone": coach.user_id.phone,
        "birthday": profile.birthday,
        "address": profile.address,
        "years_of_experience": coach.years_of_experience,
        "specialization": coach.specialization,
    }


async def get_coach_by_user_id(user_id: int) -> Optional[Dict[str, Any]]:
    """
    根据用户ID获取教练信息
    """
    coach = await Coach.get_or_none(user_id=user_id).select_related('user_id')
    if not coach:
        return None

    profile = await User_Profiles.get_or_none(user_id=coach.user_id.user_id)
    if not profile:
        return None

    return {
        "coach_id": coach.coach_id,
        "fullname": profile.fullname,
        "hire_date": coach.hire_date,
        "license_number": coach.license_number,
        "years_of_experience": coach.years_of_experience,
        "specialization": coach.specialization,
        "username": coach.user_id.username,
    }


async def create_coach(data: CoachCreateRequest) -> Dict[str, Any]:
    """
    创建新教练
    """
    # 检查用户名是否存在
    existing_user = await User.get_or_none(username=data.username)
    if existing_user:
        return {"status": 400, "message": "Username already exists"}

    try:
        async with in_transaction() as connection:
            # 创建 User
            user = await User.create(
                username=data.username,
                password=data.password,
                email=data.email,
                phone=data.phone,
                role="COACH",
                using_db=connection
            )

            # 创建 User_Profile
            await User_Profiles.create(
                user_id=user,
                fullname=data.fullname,
                gender=data.gender,
                id_number=data.id_number,
                birthday=data.birthday,
                address=data.address,
                using_db=connection
            )

            # 如果 hire_date 未提供，则设置为当前时间
            hire_date = data.hire_date if data.hire_date is not None else datetime.now().date()

            # 创建 Coach
            await Coach.create(
                user_id=user,
                hire_date=hire_date,
                license_number=data.license_number,
                years_of_experience=data.years_of_experience,
                specialization=data.specialization,
                using_db=connection
            )

        return {"status": 201, "message": "Coach created successfully"}

    except Exception as e:
        raise Exception(f"Error creating coach: {str(e)}")


async def update_coach(coach_id: int, data: CoachUpdateRequest) -> Dict[str, Any]:
    """
    更新教练信息
    """
    # 查询 Coach 并预加载关联的 User 和 User_Profiles
    coach = await Coach.get_or_none(coach_id=coach_id)
    if not coach:
        return {"status": 404, "message": "Coach not found"}

    # 加载关联的 User
    user = await User.get_or_none(user_id=coach.user_id_id)
    if not user:
        return {"status": 404, "message": "User not found"}

    # 加载关联的 User_Profiles
    profile = await User_Profiles.get_or_none(user_id=user.user_id)
    if not profile:
        return {"status": 404, "message": "Profile not found"}

    update_data = data.dict(exclude_unset=True)  # 只获取提供的字段

    # 更新 User 表
    user_fields = ['username', 'password', 'email', 'phone']
    if any(k in update_data for k in user_fields):
        # 检查新用户名是否已被占用（排除当前用户）
        if 'username' in update_data:
            existing_user = await User.get_or_none(username=update_data['username'])
            if existing_user and existing_user.user_id != user.user_id:
                return {"status": 400, "message": "Username already exists"}

        await user.update_from_dict({
            k: v for k, v in update_data.items()
            if k in user_fields
        }).save()

    # 更新 User_Profiles 表
    profile_fields = ['fullname', 'gender', 'birthday', 'address']
    if any(k in update_data for k in profile_fields):
        await profile.update_from_dict({
            k: v for k, v in update_data.items()
            if k in profile_fields
        }).save()

    # 更新 Coach 表
    coach_fields = ['hire_date', 'license_number', 'years_of_experience', 'specialization']
    if any(k in update_data for k in coach_fields):
        await coach.update_from_dict({
            k: v for k, v in update_data.items()
            if k in coach_fields
        }).save()

    return {"status": 200, "message": "Coach updated successfully"}


async def delete_coach(coach_id: int) -> Dict[str, Any]:
    """
    删除教练
    """
    coach = await Coach.get_or_none(coach_id=coach_id)
    if not coach:
        return {"status": 404, "message": "Coach not found"}

    user_id = coach.user_id_id

    try:
        async with in_transaction() as connection:
            # 删除教练
            await coach.delete(using_db=connection)

            # 删除关联的 User_Profiles
            await User_Profiles.filter(user_id=user_id).delete(using_db=connection)

            # 删除关联的 User
            await User.filter(user_id=user_id).delete(using_db=connection)

        return {"status": 200, "message": "Coach deleted successfully"}
    except Exception as e:
        raise Exception(f"Error deleting coach: {str(e)}")
